﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Filter_Topic_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Filter_Topic_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Filter_Topic_InsertUpdate]
    @FilterDbId bigint,
    @TopicDbId bigint
AS
BEGIN
    BEGIN TRY
        UPDATE Filter_Topic
        SET
            FilterDbId = @FilterDbId,
            TopicDbId = @TopicDbId
        WHERE
            FilterDbId = @FilterDbId AND
            TopicDbId = @TopicDbId

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Filter_Topic
            (
                FilterDbId,
                TopicDbId
            )
            VALUES
            (
                @FilterDbId,
                @TopicDbId
            )
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_Response_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_Response_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_Response_InsertUpdate]
    @ReceivePortDbId bigint,
    @ResponseDbId bigint
AS
BEGIN
    BEGIN TRY
        UPDATE ReceivePort_Response
        SET
            ReceivePortDbId = @ReceivePortDbId,
            ResponseDbId = @ResponseDbId
        WHERE
            ReceivePortDbId = @ReceivePortDbId AND
            ResponseDbId = @ResponseDbId

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO ReceivePort_Response
            (
                ReceivePortDbId,
                ResponseDbId
            )
            VALUES
            (
                @ReceivePortDbId,
                @ResponseDbId
            )
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Topic_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Topic_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Topic_InsertUpdate]
    @Name nvarchar(250),
    @TopicDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE Topic
        SET
            Name = @Name
        WHERE
            Name = @Name

        IF @@ROWCOUNT = 1
        BEGIN
            SET @TopicDbId = (SELECT DbId FROM Topic WHERE Name = @Name)
        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Topic
            (
                Name
            )
            VALUES
            (
                @Name
            )
            SET @TopicDbId = @@IDENTITY
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscriber_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscriber_Select]
GO
CREATE PROCEDURE [dbo].[sp_Subscriber_Select]
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
            ModificationDateTimeUtc
        FROM Client c
        --INNER JOIN Subscriber s
        --ON c.DbId = s.SubscriberDbId
        WHERE
            c.ClientTypeDbId = 0
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Filter_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Publisher_Select]
GO
CREATE PROCEDURE [dbo].[sp_Publisher_Select]
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
            ModificationDateTimeUtc
        FROM Client c
        --INNER JOIN Subscriber s
        --ON c.DbId = s.SubscriberDbId
        WHERE
            c.ClientTypeDbId = 1
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Client_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Client_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Client_InsertUpdate]
    @Id nvarchar(250),
    @ClientTypeDbId bigint,
    @ClientDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE Client
        SET
            Id = @Id,
            ClientTypeDbId = @ClientTypeDbId,
            ModificationDateTimeUtc = SYSUTCDATETIME()
        WHERE Id = @Id
        
        IF @@ROWCOUNT = 1
        BEGIN
            SET @ClientDbId = (SELECT DbId FROM Client WHERE Id = @Id)
        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Client
            (
                Id,
                ClientTypeDbId,
                ModificationDateTimeUtc
            )
            VALUES
            (
                @Id,
                @ClientTypeDbId,
                SYSUTCDATETIME()
            )
            SET @ClientDbId = @@IDENTITY
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Filter_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Filter_Select]
GO
CREATE PROCEDURE [dbo].[sp_Filter_Select]
    @ClientDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
            FilterTypeDbId,
            FilterData,
            CustomTypeName,
            (SELECT Id FROM Filter f1 WHERE f1.DbId = f.Filter1DbId) AS Filter1Id,
            (SELECT Id FROM Filter f2 WHERE f2.DbId = f.Filter2DbId) AS Filter2Id
        FROM Filter f
        WHERE
            f.ClientDbId = @ClientDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Filter_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Filter_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Filter_InsertUpdate]
    @Id nvarchar(250),
    @ClientDbId bigint,
    @FilterTypeDbId bigint,
    @FilterData nvarchar(max) = null,
    @CustomTypeName nvarchar(250) = null,
    @Filter1DbId bigint = null,
    @Filter2DbId bigint = null,
    @FilterDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE Filter
        SET
            FilterData = @FilterData,
            CustomTypeName = @CustomTypeName,
            Filter1DbId = @Filter1DbId,
            Filter2DbId = @Filter2DbId
        WHERE
            Id = @Id AND
            ClientDbId = @ClientDbId

        IF @@ROWCOUNT = 1
        BEGIN
            SET @FilterDbId =
            (
                SELECT DbId
                FROM Filter
                WHERE
                    Id = @Id AND
                    ClientDbId = @ClientDbId
            )
        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Filter
            (
                Id,
                ClientDbId,
                FilterTypeDbId,
                FilterData,
                CustomTypeName,
                Filter1DbId,
                Filter2DbId
            )
            VALUES
            (
                @Id,
                @ClientDbId,
                @FilterTypeDbId,
                @FilterData,
                @CustomTypeName,
                @Filter1DbId,
                @Filter2DbId
            )
            SET @FilterDbId = @@IDENTITY
        END

    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Filter_DeleteByClientDbId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Filter_DeleteByClientDbId]
GO
CREATE PROCEDURE [dbo].[sp_Filter_DeleteByClientDbId]
    @ClientDbId bigint
AS
BEGIN
    BEGIN TRY
        DELETE FROM Filter
        WHERE
            ClientDbId = @ClientDbId
            
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Endpoint_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Endpoint_Select]
GO
CREATE PROCEDURE [dbo].[sp_Endpoint_Select]
    @ClientDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
            Address,
            EndpointNameDbId,
            CustomEndpointName,
            TransportTypeDbId,
            CustomTransportType,
            ExchangeTypeDbId,
            TransactionScopeOptionDbId,
            Synchronous
        FROM Endpoint e
        WHERE
            e.ClientDbId = @ClientDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Endpoint_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Endpoint_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Endpoint_InsertUpdate]
    @Id nvarchar(250),
    @ClientDbId bigint,
    @Address nvarchar(250),
    @EndpointNameDbId bigint,
    @CustomEndpointName nvarchar(250) = null,
    @TransportTypeDbId bigint,
    @CustomTransportType nvarchar(250) = null,
    @ExchangeTypeDbId bigint,
    @TransactionScopeOptionDbId int,
    @Synchronous bit,
    @EndpointDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE Endpoint
        SET
            Address = @Address,
            EndpointNameDbId = @EndpointNameDbId,
            CustomEndpointName = @CustomEndpointName,
            TransportTypeDbId = @TransportTypeDbId,
            CustomTransportType = @CustomTransportType,
            ExchangeTypeDbId = @ExchangeTypeDbId,
            TransactionScopeOptionDbId = @TransactionScopeOptionDbId,
            Synchronous = @Synchronous
        WHERE
            Id = @Id AND
            ClientDbId = @ClientDbId

        IF @@ROWCOUNT = 1
        BEGIN
            SET @EndpointDbId =
            (
                SELECT DbId
                FROM Endpoint
                WHERE Id = @Id AND
                ClientDbId = @ClientDbId
            )
        END
        
        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Endpoint
            (
                Id,
                ClientDbId,
                Address,
                EndpointNameDbId,
                CustomEndpointName,
                TransportTypeDbId,
                CustomTransportType,
                ExchangeTypeDbId,
                TransactionScopeOptionDbId,
                Synchronous
            )
            VALUES
            (
                @Id,
                @ClientDbId,
                @Address,
                @EndpointNameDbId,
                @CustomEndpointName,
                @TransportTypeDbId,
                @CustomTransportType,
                @ExchangeTypeDbId,
                @TransactionScopeOptionDbId,
                @Synchronous
            )
            SET @EndpointDbId = @@IDENTITY
        END
        
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Endpoint_DeleteByClientDbId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Endpoint_DeleteByClientDbId]
GO
CREATE PROCEDURE [dbo].[sp_Endpoint_DeleteByClientDbId]
    @ClientDbId bigint
AS
BEGIN
    BEGIN TRY
        DELETE FROM Endpoint
        WHERE
            ClientDbId = @ClientDbId
            
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Publisher_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Publisher_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Publisher_InsertUpdate]
    @Id nvarchar(250),
    @ClientTypeDbId bigint,
    @PublisherDbId bigint output
AS
BEGIN
    BEGIN TRY
        DECLARE @ClientDbId bigint
        EXEC sp_Client_InsertUpdate @Id, @ClientTypeDbId, @ClientDbId output

        UPDATE Publisher
        SET
            PublisherDbId = @ClientDbId
        WHERE PublisherDbId = @ClientDbId
        
        IF @@ROWCOUNT = 1
        BEGIN
            SET @PublisherDbId = @ClientDbId
        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Publisher
            (
                PublisherDbId
            )
            VALUES
            (
                @ClientDbId
            )
            SET @PublisherDbId = @ClientDbId
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_NamespaceDefinition_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_NamespaceDefinition_Select]
GO
CREATE PROCEDURE [dbo].[sp_NamespaceDefinition_Select]
    @ClientDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Prefix,
            Namespace
        FROM NamespaceDefinition nd
        WHERE
            nd.ClientDbId = @ClientDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_NamespaceDefinition_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_NamespaceDefinition_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_NamespaceDefinition_InsertUpdate]
    @ClientDbId bigint,
    @Prefix nvarchar(250),
    @Namespace nvarchar(250),
    @NamespaceDefinitionDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE NamespaceDefinition
        SET
            Prefix = @Prefix,
            Namespace = @Namespace
        WHERE
            Prefix = @Prefix AND
            ClientDbId = @ClientDbId
        
        IF @@ROWCOUNT = 1
        BEGIN
            SET @NamespaceDefinitionDbId =
            (
                SELECT DbId
                FROM NamespaceDefinition 
                WHERE
                    Prefix = @Prefix AND
                    ClientDbId = @ClientDbId
            )
        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO NamespaceDefinition
            (
                ClientDbId,
                Prefix,
                Namespace
            )
            VALUES
            (
                @ClientDbId,
                @Prefix,
                @Namespace
            )
            SET @NamespaceDefinitionDbId = @@IDENTITY
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_NamespaceDefinition_DeleteByClientDbId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_NamespaceDefinition_DeleteByClientDbId]
GO
CREATE PROCEDURE [dbo].[sp_NamespaceDefinition_DeleteByClientDbId]
    @ClientDbId bigint
AS
BEGIN
    BEGIN TRY
        DELETE FROM NamespaceDefinition
        WHERE
            ClientDbId = @ClientDbId
            
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscriber_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscriber_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Subscriber_InsertUpdate]
    @Id nvarchar(250),
    @ClientTypeDbId bigint,
    @SubscriberDbId bigint output
AS
BEGIN
    BEGIN TRY
        DECLARE @ClientDbId bigint
        EXEC sp_Client_InsertUpdate @Id, @ClientTypeDbId, @ClientDbId output

        UPDATE Subscriber
        SET
            SubscriberDbId = @ClientDbId
        WHERE SubscriberDbId = @ClientDbId

        IF @@ROWCOUNT = 1
        BEGIN
            SET @SubscriberDbId = @ClientDbId
        END
        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Subscriber
            (
                SubscriberDbId
            )
            VALUES
            (
                @ClientDbId
            )
            SET @SubscriberDbId = @ClientDbId
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Response_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Response_Select]
GO
CREATE PROCEDURE [dbo].[sp_Response_Select]
    @PublisherDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
            ResponseAction,
            BufferSize,
            ResponseXml
        FROM Response r
        WHERE
            r.PublisherDbId = @PublisherDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Response_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Response_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Response_InsertUpdate]
    @Id nvarchar(250),
    @PublisherDbId bigint,
    @ResponseAction nvarchar(250),
    @BufferSize int,
    @ResponseXml nvarchar(max),
    @ResponseDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE Response
        SET
            ResponseAction = @ResponseAction,
            BufferSize = @BufferSize,
            ResponseXml = @ResponseXml
        WHERE
            Id = @Id AND
            PublisherDbId = @PublisherDbId

        IF @@ROWCOUNT = 1
        BEGIN
            SET @ResponseDbId =
            (
                SELECT DbId
                FROM Response
                WHERE
                    Id = @Id AND
                    PublisherDbId = @PublisherDbId
            )
        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Response
            (
                Id,
                PublisherDbId,
                ResponseAction,
                BufferSize,
                ResponseXml
            )
            VALUES
            (
                @Id,
                @PublisherDbId,
                @ResponseAction,
                @BufferSize,
                @ResponseXml
            )
            SET @ResponseDbId = @@IDENTITY
        END

    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Response_DeleteByPublisherDbId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Response_DeleteByPublisherDbId]
GO
CREATE PROCEDURE [dbo].[sp_Response_DeleteByPublisherDbId]
    @PublisherDbId bigint
AS
BEGIN
    BEGIN TRY
        DELETE FROM Response
        WHERE
            PublisherDbId = @PublisherDbId
            
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Publisher_Topic_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Publisher_Topic_Select]
GO
CREATE PROCEDURE [dbo].[sp_Publisher_Topic_Select]
    @ClientDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Name
        FROM Topic t
        INNER JOIN Publisher_Topic pt
        ON t.DbId = pt.TopicDbId
        WHERE
            pt.PublisherDbId = @ClientDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Publisher_Topic_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Publisher_Topic_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Publisher_Topic_InsertUpdate]
    @PublisherDbId bigint,
    @TopicDbId bigint
AS
BEGIN
    BEGIN TRY
        UPDATE Publisher_Topic
        SET
            PublisherDbId = @PublisherDbId,
            TopicDbId = @TopicDbId
        WHERE
            PublisherDbId = @PublisherDbId AND
            TopicDbId = @TopicDbId

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Publisher_Topic
            (
                PublisherDbId,
                TopicDbId
            )
            VALUES
            (
                @PublisherDbId,
                @TopicDbId
            )
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_Select]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_Select]
    @PublisherDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
            Paused,
            ExpiryDateTimeUtc,
            MessageBufferId,
            GenerateDefaultResponse	
        FROM ReceivePort pci
        WHERE
            pci.PublisherDbId = @PublisherDbId AND
            (
                ExpiryDateTimeUtc IS NULL OR
                ExpiryDateTimeUtc > SYSUTCDATETIME()
            )
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_Response_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_Response_Select]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_Response_Select]
    @ReceivePortDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT DISTINCT
            r.Id AS ResponseId
        FROM Response r
        INNER JOIN ReceivePort_Response ar
        ON r.DbId = ar.ResponseDbId
        WHERE
            ar.ReceivePortDbId = @ReceivePortDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Select]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_Select]
    @SubscriberDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
			Persistent,
            DeliveryModeDbId,
            Paused,
            ExpiryDateTimeUtc,
            GroupId,
            MessageValidityDurationSecs,
			DeliveryAttemptIntervalSecs,
			MaxDeliveryAttemptCount
        FROM Subscription sci
        WHERE
            sci.SubscriberDbId = @SubscriberDbId AND
            (
                ExpiryDateTimeUtc IS NULL OR
                ExpiryDateTimeUtc > SYSUTCDATETIME()
            )
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_InsertUpdate]
    @Id nvarchar(250),
    @PublisherDbId bigint,
    @Paused bit,
    @ExpiryDateTimeUtc datetime2 = null,
    @GenerateDefaultResponse bit = false,
    @MessageBufferId bigint = null,
    @ReceivePortDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE ReceivePort
        SET
            Paused = @Paused,
            ExpiryDateTimeUtc = @ExpiryDateTimeUtc,
            GenerateDefaultResponse = @GenerateDefaultResponse,
            MessageBufferId = @MessageBufferId
        WHERE
            Id = @Id AND
            PublisherDbId = @PublisherDbId

        IF @@ROWCOUNT = 1
        BEGIN
            SET @ReceivePortDbId =
            (
                SELECT DbId
                FROM ReceivePort
                WHERE
                    Id = @Id AND
                    PublisherDbId = @PublisherDbId
            )
            
            DELETE FROM ReceivePort_Filter
            WHERE
                ReceivePortDbId = @ReceivePortDbId
            
            DELETE FROM ReceivePort_Topic
            WHERE
                ReceivePortDbId = @ReceivePortDbId
        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO ReceivePort
            (
                Id,
                PublisherDbId,
                Paused,
                ExpiryDateTimeUtc,
                GenerateDefaultResponse,
                MessageBufferId
            )
            VALUES
            (
                @Id,
                @PublisherDbId,
                @Paused,
                @ExpiryDateTimeUtc,
                @GenerateDefaultResponse,
                @MessageBufferId
            )
            SET @ReceivePortDbId = @@IDENTITY
        END

    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_Filter_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_Filter_Select]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_Filter_Select]
    @ReceivePortDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT DISTINCT
            f.Id AS FilterId
        FROM Filter f
        INNER JOIN ReceivePort_Filter pf
        ON f.DbId = pf.FilterDbId
        WHERE
            pf.ReceivePortDbId = @ReceivePortDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_Filter_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_Filter_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_Filter_InsertUpdate]
    @ReceivePortDbId bigint,
    @FilterDbId bigint
AS
BEGIN
    BEGIN TRY
        UPDATE ReceivePort_Filter
        SET
            ReceivePortDbId = @ReceivePortDbId,
            FilterDbId = @FilterDbId
        WHERE
            ReceivePortDbId = @ReceivePortDbId AND
            FilterDbId = @FilterDbId

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO ReceivePort_Filter
            (
                ReceivePortDbId,
                FilterDbId
            )
            VALUES
            (
                @ReceivePortDbId,
                @FilterDbId
            )
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_DeleteByPublisherDbId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_DeleteByPublisherDbId]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_DeleteByPublisherDbId]
    @PublisherDbId bigint
AS
BEGIN
    BEGIN TRY
        DELETE FROM ReceivePort_Filter
        WHERE
            ReceivePortDbId IN
                (SELECT ReceivePortDbId FROM ReceivePort WHERE PublisherDbId = @PublisherDbId)

        DELETE FROM ReceivePort_Transformation
        WHERE
            ReceivePortDbId IN
                (SELECT ReceivePortDbId FROM ReceivePort WHERE PublisherDbId = @PublisherDbId)

        DELETE FROM ReceivePort_Topic
        WHERE
            ReceivePortDbId IN
                (SELECT ReceivePortDbId FROM ReceivePort WHERE PublisherDbId = @PublisherDbId)
                
        DELETE FROM ReceivePort_Response
        WHERE
            ReceivePortDbId IN
                (SELECT ReceivePortDbId FROM ReceivePort WHERE PublisherDbId = @PublisherDbId)
                
        DELETE FROM ReceivePort
        WHERE
            PublisherDbId = @PublisherDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_Delete]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_Delete]
    @ClientId nvarchar(250),
    @ReceivePortId nvarchar(250)
AS
BEGIN
    BEGIN TRY
        DECLARE @ClientDbId bigint
        SET @ClientDbId = (SELECT DbId FROM Client WHERE Id = @ClientId)

        DECLARE @ReceivePortDbId bigint
        SET @ReceivePortDbId = (SELECT DbId FROM ReceivePort WHERE Id = @ReceivePortId AND PublisherDbId = @ClientDbId)
        
        DELETE FROM ReceivePort_Filter
        WHERE
            ReceivePortDbId = @ReceivePortDbId
        
        DELETE FROM ReceivePort_Topic
        WHERE
            ReceivePortDbId = @ReceivePortDbId
        
        DELETE FROM ReceivePort_Response
        WHERE
            ReceivePortDbId = @ReceivePortDbId

		DELETE FROM ReceivePort_Transformation
		WHERE
		    ReceivePortDbId = @ReceivePortDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_InsertUpdate]
    @Id nvarchar(250),
	@Persistent bit,
    @SubscriberDbId bigint,
    @DeliveryModeDbId bigint,
    @Paused bit,
    @ExpiryDateTimeUtc datetime2 = null,
    @GroupId nvarchar(250) = null,
    @MessageValidityDurationSecs bigint = null,
	@DeliveryAttemptIntervalSecs bigint = null,
	@MaxDeliveryAttemptCount int = null,
    @SubscriptionDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE Subscription
        SET
		    Persistent = @Persistent,
			DeliveryAttemptIntervalSecs = @DeliveryAttemptIntervalSecs,
			MaxDeliveryAttemptCount = @MaxDeliveryAttemptCount,
            DeliveryModeDbId = @DeliveryModeDbId,
            Paused = @Paused,
            ExpiryDateTimeUtc = @ExpiryDateTimeUtc,
            GroupId = @GroupId,
            MessageValidityDurationSecs = @MessageValidityDurationSecs
        WHERE
            Id = @Id AND
            SubscriberDbId = @SubscriberDbId

        IF @@ROWCOUNT = 1
        BEGIN
            SET @SubscriptionDbId =
            (
                SELECT DbId
                FROM Subscription
                WHERE
                    Id = @Id AND
                    SubscriberDbId = @SubscriberDbId
            )

            DELETE FROM Subscription_Filter
            WHERE
                SubscriptionDbId = @SubscriptionDbId
            
            DELETE FROM Subscription_Endpoint
            WHERE
                SubscriptionDbId = @SubscriptionDbId

        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Subscription
            (
                Id,
				Persistent,
				DeliveryAttemptIntervalSecs,
				MaxDeliveryAttemptCount,
                SubscriberDbId,
                DeliveryModeDbId,
                Paused,
                ExpiryDateTimeUtc,
                GroupId,
                MessageValidityDurationSecs
            )
            VALUES
            (
                @Id,
				@Persistent,
				@DeliveryAttemptIntervalSecs,
				@MaxDeliveryAttemptCount,
                @SubscriberDbId,
                @DeliveryModeDbId,
                @Paused,
                @ExpiryDateTimeUtc,
                @GroupId,
                @MessageValidityDurationSecs
            )
            SET @SubscriptionDbId = @@IDENTITY
        END

    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_Filter_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Filter_Select]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_Filter_Select]
    @SubscriptionDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT DISTINCT
            f.Id AS FilterId
        FROM Filter f
        INNER JOIN Subscription_Filter sf
        ON f.DbId = sf.FilterDbId
        WHERE
            sf.SubscriptionDbId = @SubscriptionDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_Filter_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Filter_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_Filter_InsertUpdate]
    @SubscriptionDbId bigint,
    @FilterDbId bigint
AS
BEGIN
    BEGIN TRY
        UPDATE Subscription_Filter
        SET
            SubscriptionDbId = @SubscriptionDbId,
            FilterDbId = @FilterDbId
        WHERE
            SubscriptionDbId = @SubscriptionDbId AND
            FilterDbId = @FilterDbId

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Subscription_Filter
            (
                SubscriptionDbId,
                FilterDbId
            )
            VALUES
            (
                @SubscriptionDbId,
                @FilterDbId
            )
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_Endpoint_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Endpoint_Select]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_Endpoint_Select]
    @SubscriptionDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT DISTINCT
            e.Id AS EndpointId
        FROM Endpoint e
        INNER JOIN Subscription_Endpoint se
        ON e.DbId = se.EndpointDbId
        WHERE
            se.SubscriptionDbId = @SubscriptionDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_Endpoint_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Endpoint_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_Endpoint_InsertUpdate]
    @SubscriptionDbId bigint,
    @EndpointDbId bigint
AS
BEGIN
    BEGIN TRY
        UPDATE Subscription_Endpoint
        SET
            SubscriptionDbId = @SubscriptionDbId,
            EndpointDbId = @EndpointDbId
        WHERE
            SubscriptionDbId = @SubscriptionDbId AND
            EndpointDbId = @EndpointDbId

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Subscription_Endpoint
            (
                SubscriptionDbId,
                EndpointDbId
            )
            VALUES
            (
                @SubscriptionDbId,
                @EndpointDbId
            )
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_DeleteBySubscriberDbId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_DeleteBySubscriberDbId]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_DeleteBySubscriberDbId]
    @SubscriberDbId bigint
AS
BEGIN
    BEGIN TRY
        DELETE FROM Subscription_Filter
        WHERE
            SubscriptionDbId IN
                (SELECT SubscriptionDbId FROM Subscription WHERE SubscriberDbId = @SubscriberDbId)
        
        DELETE FROM Subscription_Endpoint
        WHERE
            SubscriptionDbId IN
                (SELECT SubscriptionDbId FROM Subscription WHERE SubscriberDbId = @SubscriberDbId)

		DELETE FROM Subscription_Transformation
        WHERE
            SubscriptionDbId IN
                (SELECT SubscriptionDbId FROM Subscription WHERE SubscriberDbId = @SubscriberDbId)
                
        DELETE FROM Subscription
        WHERE
            SubscriberDbId = @SubscriberDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Delete]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_Delete]
    @ClientId nvarchar(250),
    @SubscriptionId nvarchar(250)
AS
BEGIN
    BEGIN TRY
        DECLARE @ClientDbId bigint
        SET @ClientDbId = (SELECT DbId FROM Client WHERE Id = @ClientId)

        DECLARE @SubscriptionDbId bigint
        SET @SubscriptionDbId = (SELECT DbId FROM Subscription WHERE Id = @SubscriptionId AND SubscriberDbId = @ClientDbId)
        
        DELETE FROM Subscription_Filter
        WHERE
            SubscriptionDbId = @SubscriptionDbId

		DELETE FROM Subscription_Transformation
        WHERE
            SubscriptionDbId = @SubscriptionDbId
        
        DELETE FROM Subscription_Endpoint
        WHERE
            SubscriptionDbId = @SubscriptionDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_Topic_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_Topic_Select]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_Topic_Select]
    @ReceivePortDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT DISTINCT
            t.Name AS TopicName
        FROM Topic t
        INNER JOIN ReceivePort_Topic pt
        ON t.DbId = pt.TopicDbId
        WHERE
            pt.ReceivePortDbId = @ReceivePortDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_Topic_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_Topic_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_Topic_InsertUpdate]
    @ReceivePortDbId bigint,
    @TopicDbId bigint
AS
BEGIN
    BEGIN TRY
        UPDATE ReceivePort_Topic
        SET
            ReceivePortDbId = @ReceivePortDbId,
            TopicDbId = @TopicDbId
        WHERE
            ReceivePortDbId = @ReceivePortDbId AND
            TopicDbId = @TopicDbId

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO ReceivePort_Topic
            (
                ReceivePortDbId,
                TopicDbId
            )
            VALUES
            (
                @ReceivePortDbId,
                @TopicDbId
            )
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Client_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Client_Delete]
GO
CREATE PROCEDURE [dbo].[sp_Client_Delete]
    @ClientId nvarchar(250)
AS
BEGIN
    BEGIN TRY
        DECLARE @ClientDbId bigint
        SET @ClientDbId = (SELECT DbId FROM Client WHERE Id = @ClientId)
        
        EXEC sp_Subscription_DeleteBySubscriberDbId @ClientDbId
        
        EXEC sp_ReceivePort_DeleteByPublisherDbId @ClientDbId
        DELETE FROM Publisher_Topic WHERE PublisherDbId = @ClientDbId
        
        EXEC sp_NamespaceDefinition_DeleteByClientDbId @ClientDbId

        EXEC sp_Filter_DeleteByClientDbId @ClientDbId

		EXEC sp_Transformation_DeleteByclientDbId @ClientDbId

        EXEC sp_Endpoint_DeleteByClientDbId @ClientDbId
        
        EXEC sp_Response_DeleteByPublisherDbId @ClientDbId
        
        DELETE FROM Publisher WHERE PublisherDbId = @ClientDbId
        DELETE FROM Subscriber WHERE SubscriberDbId = @ClientDbId
        DELETE FROM Client WHERE DbId = @ClientDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ClearAllSubscribers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ClearAllSubscribers]
GO
CREATE PROCEDURE [dbo].[sp_ClearAllSubscribers]
AS
    BEGIN
        SET NOCOUNT ON;

        BEGIN TRY
        
            DECLARE c1 CURSOR READ_ONLY
            FOR
            SELECT Id
            FROM Client
            WHERE ClientTypeDbId = 0

            OPEN c1

            DECLARE @ClientId nvarchar(250)
            FETCH NEXT FROM c1
            INTO @ClientId

            WHILE @@FETCH_STATUS = 0
            BEGIN
                EXEC sp_Client_Delete @ClientId
                FETCH NEXT FROM c1
                INTO @ClientId
            END
            
            CLOSE c1
            DEALLOCATE c1
        END TRY
        BEGIN CATCH
            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            -- Use RAISERROR inside the CATCH block to return 
            -- error information about the original error that 
            -- caused execution to jump to the CATCH block.
            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ClearAllPublishers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ClearAllPublishers]
GO
CREATE PROCEDURE [dbo].[sp_ClearAllPublishers]
AS
    BEGIN
        SET NOCOUNT ON;

        BEGIN TRY
        
            DECLARE c1 CURSOR READ_ONLY
            FOR
            SELECT Id
            FROM Client
            WHERE ClientTypeDbId = 1

            OPEN c1

            DECLARE @ClientId nvarchar(250)
            FETCH NEXT FROM c1
            INTO @ClientId

            WHILE @@FETCH_STATUS = 0
            BEGIN
                EXEC sp_Client_Delete @ClientId
                FETCH NEXT FROM c1
                INTO @ClientId
            END
            
            CLOSE c1
            DEALLOCATE c1
        END TRY
        BEGIN CATCH
            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            -- Use RAISERROR inside the CATCH block to return 
            -- error information about the original error that 
            -- caused execution to jump to the CATCH block.
            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Transformation_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Transformation_Select]
GO
CREATE PROCEDURE [dbo].[sp_Transformation_Select]
    @ClientDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
            TransformationTypeDbId,
            TransformationData,
            CustomTypeName,
			SoapAction
        FROM Transformation t
        WHERE
            t.ClientDbId = @ClientDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Transformation_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Transformation_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Transformation_InsertUpdate]
    @Id nvarchar(250),
    @ClientDbId bigint,
    @TransformationTypeDbId bigint,
    @TransformationData nvarchar(max) = null,
    @CustomTypeName nvarchar(250) = null,
	@SoapAction nvarchar(250) = null,
    @TransformationDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE Transformation
        SET
            TransformationData = @TransformationData,
            CustomTypeName = @CustomTypeName,
			SoapAction = @SoapAction
        WHERE
            Id = @Id AND
            ClientDbId = @ClientDbId

        IF @@ROWCOUNT = 1
        BEGIN
            SET @TransformationDbId =
            (
                SELECT DbId
                FROM Transformation
                WHERE
                    Id = @Id AND
                    ClientDbId = @ClientDbId
            )
        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Transformation
            (
                Id,
                ClientDbId,
                TransformationTypeDbId,
                TransformationData,
                CustomTypeName,
				SoapAction
            )
            VALUES
            (
                @Id,
                @ClientDbId,
                @TransformationTypeDbId,
                @TransformationData,
                @CustomTypeName,
				@SoapAction
            )
            SET @TransformationDbId = @@IDENTITY
        END

    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Transformation_DeleteByClientDbId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Transformation_DeleteByClientDbId]
GO
CREATE PROCEDURE [dbo].[sp_Transformation_DeleteByClientDbId]
    @ClientDbId bigint
AS
BEGIN
    BEGIN TRY
        DELETE FROM Transformation
        WHERE
            ClientDbId = @ClientDbId
            
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_Transformation_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_Transformation_Select]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_Transformation_Select]
    @ReceivePortDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT DISTINCT
            t.Id AS TransformationId,
			pt.TransformResponse
        FROM Transformation t
        INNER JOIN ReceivePort_Transformation pt
        ON t.DbId = pt.TransformationDbId
        WHERE
            pt.ReceivePortDbId = @ReceivePortDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_Transformation_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_Transformation_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_Transformation_InsertUpdate]
    @ReceivePortDbId bigint,
    @TransformationDbId bigint,
	@TransformResponse bit
AS
BEGIN
    BEGIN TRY
        UPDATE ReceivePort_Transformation
        SET
            ReceivePortDbId = @ReceivePortDbId,
            TransformationDbId = @TransformationDbId,
			TransformResponse = @TransformResponse
        WHERE
            ReceivePortDbId = @ReceivePortDbId AND
            TransformationDbId = @TransformationDbId

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO ReceivePort_Transformation
            (
                ReceivePortDbId,
                TransformationDbId,
				TransformResponse
            )
            VALUES
            (
                @ReceivePortDbId,
                @TransformationDbId,
				@TransformResponse
            )
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_Transformation_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Transformation_Select]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_Transformation_Select]
    @SubscriptionDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT DISTINCT
            t.Id AS TransformationId,
			st.TransformResponse
        FROM Transformation t
        INNER JOIN Subscription_Transformation st
        ON t.DbId = st.TransformationDbId
        WHERE
            st.SubscriptionDbId = @SubscriptionDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_Transformation_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Transformation_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_Transformation_InsertUpdate]
    @SubscriptionDbId bigint,
    @TransformationDbId bigint,
	@TransformResponse bit
AS
BEGIN
    BEGIN TRY
        UPDATE Subscription_Transformation
        SET
            SubscriptionDbId = @SubscriptionDbId,
            TransformationDbId = @TransformationDbId,
			TransformResponse = @TransformResponse
        WHERE
            SubscriptionDbId = @SubscriptionDbId AND
            TransformationDbId = @TransformationDbId

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Subscription_Transformation
            (
                SubscriptionDbId,
                TransformationDbId,
				TransformResponse
            )
            VALUES
            (
                @SubscriptionDbId,
                @TransformationDbId,
				@TransformResponse
            )
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO
